﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using RealtorsPortal.Data_Access_Layer;
using System.Data;
using RealtorsPortal.Entities;

namespace RealtorsPortal.Business_Logic_Layer
{
    public class AdvertisementsDAL
    {
        DataAccessHelper daAccHelp = new DataAccessHelper();

        public DataTable getAdvertisementsData()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Advertisement");
            return dt;
        }

        public DataTable GetPackageIdByAdvId(AdvertisementsDAO advObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> lstParams = new List<SqlParameter>();
            SqlParameter param;

            param = new SqlParameter("@AdvId", SqlDbType.VarChar);
            param.Value = advObj.AdvId;
            lstParams.Add(param);
            dt = daAccHelp.GetData("sp_Get_PackageId_By_AdvId", lstParams);
            return dt;
        }

        public int GetLastAdID()
        {
            return daAccHelp.GetLastID("sp_GetAdvertisementsLastID", "AdvId") + 1;
        }

        public DataTable GetAdByTile(AdvertisementsDAO advObj) {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@Title", SqlDbType.NVarChar);
            para.Value = advObj.Title;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_Many_Table_With_Title", paraList);
        }

        public DataTable GetAdByCateId(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CategoryId", SqlDbType.NVarChar);
            para.Value = advObj.CategoryId;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_Many_Table_With_CategoryId", paraList);
        }

        public int UpdateDuration(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@AdvId", SqlDbType.Int);
            para.Value = advObj.AdvId;
            paraList.Add(para);
            para = new SqlParameter("@ExpDate", SqlDbType.NVarChar);
            para.Value = advObj.ExpDate;
            paraList.Add(para);

            return daAccHelp.ExcuteStoredNonQuery("sp_UpdateDuration", paraList);
        }




        public DataTable GetAdvertisementDetails(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@AdvId", SqlDbType.Int);
            para.Value = advObj.AdvId;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_Advertisements_Details", paraList);
        }


        public DataTable GetPackNameByAdvId(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@AdvId", SqlDbType.Int);
            para.Value = advObj.AdvId;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_PackagNameByAdvId", paraList);
        }


        public DataTable GetImageUrlById(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@AdvId", SqlDbType.Int);
            para.Value = advObj.AdvId;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_ImageUrl_By_Id", paraList);
        }

        public DataTable GetUnPaidAdsDataTable()
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.[Type], a.PostedDate,p.PackageName, p.Price, s.IsPaid
                           from Subscription as s, Packages as p, Advertisements as a
                           where s.PackageId = p.PackageId and a.PackageId = p.PackageId and p.Price>0 and a.PostedDate = s.SubDate and a.IsActive ='True' and s.IsPaid ='False'";
            return daAccHelp.GetDataTable(cmd);
        }



        public DataTable SearchBasicAds(string key)
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and a.Title LIKE '%" + key + "%'";
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable SearchAdvancedAds(string key)
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True'" + key;
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetFeaturedAdsDataTable()
        {
            string cmd = @"select distinct a.AdvId,a.Type, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and a.IsFeatured = 'True'";
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetAdsByCateDataTable(string cateId)
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and a.CategoryId =" + cateId;
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetAdsByTypeDataTable(string type)
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and a.Type='" + type + "'";
            return daAccHelp.GetDataTable(cmd);
        }


        public DataTable GetVIPAdsDataTable()
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and p.Price <> 0";
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetNormalAdsDataTable()
        {
            string cmd = @"select distinct a.AdvId, a.Title, a.ImageUrl,a.[Address], d.DistrictName, c.CityName, a.Price, ct.CategoryName
                    from Advertisements as a, Districts as d, Cities as c, Categories as ct, Packages as p, Subscription as s
                    where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.IsActive='True' and p.Price = 0";
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetAdsByAdvId(string advId)
        {
            string cmd = @"select top 1 a.*, d.DistrictName, c.CityName, ct.CategoryName, u.Username, u.Phone, u.Email, a.AdvId
from Advertisements as a, Districts as d, Cities as c, Categories as ct, Users as u, Packages as p, Subscription as s
 where a.CategoryId = ct.CategoryId and a.DistrictId = d.DistrictId and c.CityId = d.CityId and a.UserId = u.UserId and
  a.IsActive='True' and p.PackageId = a.PackageId and s.PackageId = p.PackageId and s.IsPaid='True' and a.AdvId = " + advId;
            return daAccHelp.GetDataTable(cmd);
        }

        public DataTable GetAdvertisements(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@UserId", SqlDbType.Int);
            para.Value = advObj.UserId;
            paraList.Add(para);
            return daAccHelp.GetData("sp_Get_Many_Table_With_Advertisements", paraList);
        }

        public DataTable GetAdverIdByUserId(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@UserId", SqlDbType.Int);
            para.Value = advObj.UserId;
            paraList.Add(para);

            return daAccHelp.GetData("sp_Get_AdvId_By_UserId", paraList);
        }

        public int InsertAvertisements(AdvertisementsDAO advObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryAdvertisementsToInsert(advObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Insert_Advertisement", pList);
        }

        public int UpdateAdvertisements(AdvertisementsDAO advObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryAdvertisements(advObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Update_Advertisements", pList);
        }

        public int DeleteAdvertisements(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@AdvId", SqlDbType.NVarChar);
            para.Value = advObj.AdvId;
            paraList.Add(para);

            return daAccHelp.ExcuteStoredNonQuery("sp_Delete_Advertisements", paraList);
        }

        private List<SqlParameter> ExecuteNonQueryAdvertisements(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@AdvId", SqlDbType.NVarChar);
            para.Value = advObj.AdvId;
            paraList.Add(para);
            para = new SqlParameter("@CategoryId", SqlDbType.NVarChar);
            para.Value = advObj.CategoryId;
            paraList.Add(para);
            para = new SqlParameter("@UserId", SqlDbType.NVarChar);
            para.Value = advObj.UserId;
            paraList.Add(para);
            para = new SqlParameter("@DistrictId", SqlDbType.NVarChar);
            para.Value = advObj.DistrictId;
            paraList.Add(para);
            para = new SqlParameter("@PackageId", SqlDbType.NVarChar);
            para.Value = advObj.ParkageId;
            paraList.Add(para);
            para = new SqlParameter("@Title", SqlDbType.NVarChar);
            para.Value = advObj.Title;
            paraList.Add(para);
            para = new SqlParameter("@Type", SqlDbType.NVarChar);
            para.Value = advObj.Type;
            paraList.Add(para);
            para = new SqlParameter("@ImageUrl", SqlDbType.NVarChar);
            para.Value = advObj.ImageUrl;
            paraList.Add(para);
            para = new SqlParameter("@Price", SqlDbType.NVarChar);
            para.Value = advObj.Price;
            paraList.Add(para);
            para = new SqlParameter("@PostedDate", SqlDbType.NVarChar);
            para.Value = advObj.PostedDate;
            paraList.Add(para);
            para = new SqlParameter("@ExpDate", SqlDbType.NVarChar);
            para.Value = advObj.ExpDate;
            paraList.Add(para);
            para = new SqlParameter("@LandArea", SqlDbType.NVarChar);
            para.Value = advObj.LandArea;
            paraList.Add(para);
            para = new SqlParameter("@Floor", SqlDbType.NVarChar);
            para.Value = advObj.Floor;
            paraList.Add(para);
            para = new SqlParameter("@BedRoom", SqlDbType.NVarChar);
            para.Value = advObj.Bedroom;
            paraList.Add(para);
            para = new SqlParameter("@BathRoom", SqlDbType.NVarChar);
            para.Value = advObj.Bathroom;
            paraList.Add(para);
            para = new SqlParameter("@BuiltYear", SqlDbType.NVarChar);
            para.Value = advObj.BuiltYear;
            paraList.Add(para);
            para = new SqlParameter("@Address", SqlDbType.NVarChar);
            para.Value = advObj.Address;
            paraList.Add(para);
            para = new SqlParameter("@Description", SqlDbType.NVarChar);
            para.Value = advObj.Description;
            paraList.Add(para);
            para = new SqlParameter("@Amenities", SqlDbType.NVarChar);
            para.Value = advObj.Amenities;
            paraList.Add(para);
            para = new SqlParameter("@IsFeatured", SqlDbType.NVarChar);
            para.Value = advObj.IsFeatured;
            paraList.Add(para);
            para = new SqlParameter("@IsActive", SqlDbType.NVarChar);
            para.Value = advObj.IsActive;
            paraList.Add(para);

            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryAdvertisementsToInsert(AdvertisementsDAO advObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@CategoryId", SqlDbType.NVarChar);
            para.Value = advObj.CategoryId;
            paraList.Add(para);
            para = new SqlParameter("@UserId", SqlDbType.NVarChar);
            para.Value = advObj.UserId;
            paraList.Add(para);
            para = new SqlParameter("@DistrictId", SqlDbType.NVarChar);
            para.Value = advObj.DistrictId;
            paraList.Add(para);
            para = new SqlParameter("@PackageId", SqlDbType.NVarChar);
            para.Value = advObj.ParkageId;
            paraList.Add(para);
            para = new SqlParameter("@Title", SqlDbType.NVarChar);
            para.Value = advObj.Title;
            paraList.Add(para);
            para = new SqlParameter("@Type", SqlDbType.NVarChar);
            para.Value = advObj.Type;
            paraList.Add(para);
            para = new SqlParameter("@ImageUrl", SqlDbType.NVarChar);
            para.Value = advObj.ImageUrl;
            paraList.Add(para);
            para = new SqlParameter("@Price", SqlDbType.NVarChar);
            para.Value = advObj.Price;
            paraList.Add(para);
            para = new SqlParameter("@PostedDate", SqlDbType.NVarChar);
            para.Value = advObj.PostedDate;
            paraList.Add(para);
            para = new SqlParameter("@ExpDate", SqlDbType.NVarChar);
            para.Value = advObj.ExpDate;
            paraList.Add(para);
            para = new SqlParameter("@LandArea", SqlDbType.NVarChar);
            para.Value = advObj.LandArea;
            paraList.Add(para);
            para = new SqlParameter("@Floor", SqlDbType.NVarChar);
            para.Value = advObj.Floor;
            paraList.Add(para);
            para = new SqlParameter("@BedRoom", SqlDbType.NVarChar);
            para.Value = advObj.Bedroom;
            paraList.Add(para);
            para = new SqlParameter("@BathRoom", SqlDbType.NVarChar);
            para.Value = advObj.Bathroom;
            paraList.Add(para);
            para = new SqlParameter("@BuiltYear", SqlDbType.NVarChar);
            para.Value = advObj.BuiltYear;
            paraList.Add(para);
            para = new SqlParameter("@Address", SqlDbType.NVarChar);
            para.Value = advObj.Address;
            paraList.Add(para);
            para = new SqlParameter("@Description", SqlDbType.NVarChar);
            para.Value = advObj.Description;
            paraList.Add(para);
            para = new SqlParameter("@Amenities", SqlDbType.NVarChar);
            para.Value = advObj.Amenities;
            paraList.Add(para);
            para = new SqlParameter("@IsFeatured", SqlDbType.NVarChar);
            para.Value = advObj.IsFeatured;
            paraList.Add(para);
            para = new SqlParameter("@IsActive", SqlDbType.NVarChar);
            para.Value = advObj.IsActive;
            paraList.Add(para);
            return paraList;
        }

        public bool PayAdvertisement(int advId)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@advId", advId));
            return daAccHelp.ExcuteStoredNonQuery("sp_payAdvertisement", list) > 0;
        }

        public bool DisableFeatured(int advId)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@advId", advId));
            return daAccHelp.ExcuteStoredNonQuery("sp_DisableFeatured", list) > 0;
        }
    }
}